package com.li.springbootproject.service.jdbctemplate;

import com.li.springbootproject.domain.TestOne;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author LiDong
 * @createtime 2021-06-01 14:43:19
 */
@Service
public class JdbcTemplateServiceImpl implements JdbcTemplateService {

    private static final Logger logger = LoggerFactory.getLogger(JdbcTemplateServiceImpl.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * JdbcTemplate 使用RowCallBachHandler方式处理查询结果
     */
    @Override
    public void queryRowCallBack() {
        String sql = "select * from testone";
        // 使用RowCallBackHandler的方式处理查询结果
        List<TestOne> list = new ArrayList<>();
        jdbcTemplate.query(sql, new OneResultRowCallBackHandler(list));
        logger.info("Results:{}", list);
    }

    /**
     * 使用RowMapper 处理查询结果
     */
    @Override
    public void queryRowMapper() {
        String sql = "select * from testone";
        List<TestOne> list = jdbcTemplate.query(sql, (resultSet, i) -> {
            TestOne testOne = new TestOne();
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String sno = resultSet.getString("sno");
            testOne.setId(id);
            testOne.setName(name);
            testOne.setAge(age);
            testOne.setSno(sno);
            return testOne;
        });
        logger.info("Results:{}", list);
    }

    @Override
    public void queryResultSetExtractor() {
        String sql = "select * from testone";
        List<TestOne> list = new ArrayList<>();
        jdbcTemplate.query(sql, (ResultSetExtractor) resultSet -> {
            while (resultSet.next()) {
                TestOne testOne = new TestOne();
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String sno = resultSet.getString("sno");
                testOne.setId(id);
                testOne.setName(name);
                testOne.setAge(age);
                testOne.setSno(sno);
                list.add(testOne);
            }
            return list;
        });
        logger.info("Results:{}", list);
    }

    /**
     * 占位符形式操作
     */
    @Override
    public void insertTestOne() {
        String sql = " insert into testOne (name, age, sno) values(?,?,?)";
        jdbcTemplate.update(sql, "张三", 23, "1001");
    }


    @Override
    public void insertTestTwo() {
        String sql = "insert into testOne (name,age, sno) values('121',22,121)";
        jdbcTemplate.update(sql);
    }


    private class OneResultRowCallBackHandler implements RowCallbackHandler {

        private final List<TestOne> list;

        public OneResultRowCallBackHandler(List<TestOne> list) {
            this.list = list;
        }

        @Override
        public void processRow(ResultSet resultSet) throws SQLException {
            TestOne testOne = new TestOne();
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String sno = resultSet.getString("sno");
            testOne.setId(id);
            testOne.setName(name);
            testOne.setAge(age);
            testOne.setSno(sno);
            list.add(testOne);
        }
    }


}
